const Database = require('better-sqlite3');
const path = require('path');

const dbPath = path.join(__dirname, '..', 'data', 'delivery-management.db');
const db = new Database(dbPath);

try {
  // Check if sub_specialty column exists
  const tableInfo = db.prepare('PRAGMA table_info(devices)').all();
  const hasSubSpecialty = tableInfo.some(col => col.name === 'sub_specialty');

  if (!hasSubSpecialty) {
    db.exec('ALTER TABLE devices ADD COLUMN sub_specialty TEXT');
    console.log('Added sub_specialty column to devices table');
  } else {
    console.log('sub_specialty column already exists');
  }

  // Define piping sub-specialty mapping based on classification_name
  const pipingSubSpecialtyMapping = {
    '给排水': [
      '含油污水处理设备',
      '生活污水处理设备',
      '离心泵',
      '潜液泵',
      '滑片泵',
      '转子泵'
    ],
    '消防': [
      '消防稳压设备',
      '消防设备',
      '泡沫比例混合装置',
      '空气泡沫发生器',
      '冷却水喷淋装置'
    ],
    '工艺': [
      '圆筒形储罐',
      '橇',
      '管线/管道',
      '管道过滤器',
      '软管',
      '金属波纹管',
      '鹤管',
      '阻火器'
    ],
    '配管': [
      '安全阀',
      '呼吸阀',
      '止回阀',
      '水力控制阀',
      '球阀',
      '蝶阀',
      '闸阀',
      '阀门'
    ]
  };

  // Update existing piping devices with sub_specialty
  const updateStmt = db.prepare('UPDATE devices SET sub_specialty = ? WHERE specialty = ? AND classification_name = ?');

  for (const [subSpecialty, classificationNames] of Object.entries(pipingSubSpecialtyMapping)) {
    for (const classificationName of classificationNames) {
      const result = updateStmt.run(subSpecialty, '配管专业', classificationName);
      console.log(`Updated ${result.changes} records for ${classificationName} -> ${subSpecialty}`);
    }
  }

  // Verify the updates
  const verification = db.prepare('SELECT sub_specialty, COUNT(*) as count FROM devices WHERE specialty = ? GROUP BY sub_specialty').all('配管专业');
  console.log('Verification - Sub-specialty distribution:', verification);

} catch (error) {
  console.error('Error:', error);
} finally {
  db.close();
}